from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import json

engine = create_engine(url='mysql+pymysql://root:123!#Qw@localhost/press?charset=utf8', echo=False)
session = sessionmaker(bind=engine)()
Base = declarative_base()


class Author(Base):
    """
    主类：作者
    """
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    email = Column(String(64), nullable=False, index=True)
    authorinfo = relationship('AuthorInfo', backref='author', uselist=False)

    def __repr__(self):
        return f'作者：{self.name},{self.authorinfo}'


class AuthorInfo(Base):
    """
    副类：作者信息
    """
    __tablename__ = 'authorinfo'

    id = Column(Integer, primary_key=True)
    qq = Column(String(11))
    phone = Column(String(11))
    link = Column(String(64))
    author_id = Column(Integer, ForeignKey('author.id'))

    def __repr__(self):
        return f'联系QQ：{self.qq},联系电话：{self.phone}'


Base.metadata.create_all(engine)

# session.add_all([
#     Author(name='张三', password='123', email='342656@qq.com'),
#     Author(name='李四', password='wes', email='562656@qq.com'),
#     AuthorInfo(qq='342656', phone='18523348888', author_id=1),
#     AuthorInfo(qq='562656', phone='18678659876', author_id=2),
# ])

session.commit()
# 查询
author_one = session.query(Author).filter(Author.id == 1).all()
print(author_one)